<?php
class model_news extends framework_base_model {

	private static $table = 'weixin_news';
	private static $category_table = 'weixin_category';
	
	/**
	 * 获取栏目列表
	 * @return array 栏目列表
	 */
	public static function getCategoryList() {
		$sql = "select * from ".self::$category_table." where delete_flag=0 and is_show = 1";
		$category_list = array();
		try {
			$stmt = self::DB() -> dbh -> prepare($sql);
			$stmt->execute();
			$category_list = $stmt -> fetchAll();
		}catch (PDOException $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $category_list;
	}
	//获取子栏目
	public static function get_category_child($parent_id){
		$sql = "select `category_id`,`category_name` from ".self::$category_table." where parent_id = :parent_id";
		$category_list = array();
		try {
			$stmt = self::DB() -> dbh -> prepare($sql);
			$stmt->execute(array(":parent_id" => $parent_id));
			$category_list = $stmt -> fetchAll();
		}catch (PDOException $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $category_list;
	}
	//获取子栏目父类ID
	public static function get_category_parents($chid_id){
		$sql = "select `parent_id` from ".self::$category_table." where category_id = :category_id";
		$category = 0;
		try {
			$stmt = self::DB() -> dbh -> prepare($sql);
			$stmt->execute(array(":category_id" => $chid_id));
			$category = $stmt -> fetchColumn();
		}catch (PDOException $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $category;
	}
	/**
	 * 添加新闻
	 * @param array $data
	 * @return number
	 */
	public static function newsAdd($data) {
    	if(!is_array($data)) {
    		return 0;
    	}
		//$visible = $data['visible'];
		//unset($data['visible']);
    	try{
    		$sql = "INSERT INTO ".self::$table." SET ";
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		$sql .= 'raw_add_time=NOW()';
			$sql1 = "insert into `weixin_news_category` (`news_id`,`category_id`,`raw_add_time`) values(:news_id,:category_id,NOW())";
			$pdo = self::DB() -> dbh;
			$pdo -> beginTransaction();
    		$stmt= $pdo -> prepare($sql);
    		$rel = $stmt->execute($binds);
			/*$id = $pdo->lastInsertId();
			if(!empty($visible)){
				$stmt1 = $pdo -> prepare($sql1);
				foreach($visible as $v){
					$binds = array();
					$binds[":news_id"] = $id;
					$binds[":category_id"] = $v;
					$stmt1->execute($binds);
				}
			}*/
			$pdo -> commit();
			return true;
    	}catch(Exception $e){
			$pdo -> rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return 0;
    	}
    }
    /*
     * 根据ID获取新闻内容
     */
    public static function getNewsInfoById($id) {
    	$info = array();
    	if(intval($id) <= 0) {
    		return $info;
    	}
    	$sql = "select * from ".self::$table." where id=:id and delete_flag = 0";
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(":id" => intval($id)));
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
    /**
     * 更新文章内容
     * @param unknown $id
     * @param unknown $data
     * @return unknown|number
     */
    public static function updateNewsInfo($id,$data) {
		if(!is_array($data) || empty($id)) {
    		return 0;
    	}
		//$visible = $data['visible'];
		//unset($data['visible']);
    	try{
    		$sql = 'UPDATE '.self::$table.' SET ';
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		//$ids = implode(',',$id);
    		$sql = trim($sql,',');
    		$sql .= ' WHERE `id` in ('.$id.')';
			$sql1 = "update `weixin_news_category` set `delete_flag` = :flag where `news_id` = :new_id";
			$sql2 = "update `weixin_news_category` set `delete_flag` = :flag where `news_id` = :new_id and `category_id` = :category_id";
			$sql3 = "insert into `weixin_news_category` (`news_id`,`category_id`,`raw_add_time`) values(:news_id,:category_id,NOW())";
			$pdo = self::DB() -> dbh;
			$pdo -> beginTransaction();
			
    		$stm= $pdo -> prepare($sql);
    		$rel = $stm -> execute($binds);
    		
			/*$stm1 = $pdo -> prepare($sql1);
			$stm1 -> execute(array(':flag'=>1,':new_id'=>$id));

			if(!empty($visible)){
				$stm3 = $pdo -> prepare($sql3);
				foreach($visible as $v){
					$stm2 = $pdo -> prepare($sql2);
					$stm2 -> execute(array(':flag'=>0,':new_id'=>$id, ':category_id' => $v));
					$rowCount = $stm2 -> rowCount();
					
					if($rowCount <= 0){
						$stm3->execute(array(':news_id'=>$id,':category_id'=>$v));
					}
				}
			}*/
			$pdo -> commit();
    		return $rowCount;
    	}catch(Exception $e){
				$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return -1;
    	}
    }
				/**
     * 更新文章内容
     * @param unknown $id
     * @param unknown $data
     * @return unknown|number
     */
    public static function delete_news(array $id) {
		if(empty($id)) {
    		return 0;
    	}
		$ids = implode(',', $id);
    	try{
			$sql1 = "update `weixin_news` set `delete_flag` = 1 where `id` in ({$ids})";
			//$sql2 = "update `weixin_news_category` set `delete_flag` = 1 where `news_id` in ({$ids})";
			
			$pdo = self::DB() -> dbh;
			$pdo -> beginTransaction();
			
    		$stm1= $pdo -> prepare($sql1);
    		$stm1 -> execute();
    		
			//$stm2 = $pdo -> prepare($sql2);
			//$stm2 -> execute();

			$pdo -> commit();
    		return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return -1;
    	}
    }
    /**
     * 获取新闻列表
     * @param number $nextNum
     * @param number $pagesize
     * @param unknown $condition
     * @return unknown|Ambigous <number, unknown>
     */
    public static function newsList($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$sql = "SELECT a.`id`,a.`title`,a.show_cover_pic,a.content_source_url,a.category_id,a.author,a.raw_add_time,a.raw_update_time,b.`category_name`,b.parent_id FROM `".self::$table . "` AS a,`".self::$category_table."` AS b WHERE a.category_id = b.category_id AND a.delete_flag = 0 ";
    	if(is_array($condition)&& count($condition)>0){
    		
    		$keywords = trim($condition['keyword']);
    		if(!empty($keywords)) {
    			if($condition['search_type'] == 'title') {
    				$sql .= " AND a.`title` like '%".$keywords."%'";
    			} else {
    				$sql .= " AND a.`content` like '%".$keywords."%'";
    			}
    		}
			//小栏目
			if(isset($condition['category_parent_id'])) {
    			$sql .= " AND a.category_id=:category_id";
    			$binds[':category_id'] = $condition['category_parent_id'];
			//大栏目
    		}else{
				if(isset($condition['category_id'])) {
					$child = model_news::get_category_child($condition['category_id']);
					if(!empty($child)){
						$ids[] = $condition['category_id'];
						foreach($child as $v){
							$ids[] = $v['category_id'];
						}
						$ids = implode(",", $ids);
						$sql .= " AND a.category_id in ({$ids}) ";
					}else{
						$sql .= " AND a.category_id=:category_id ";
						$binds[':category_id'] = $condition['category_id'];
					}
				}
			}
    	}
    	$sql .= " ORDER BY a.`id` DESC";
    	
        if($pagesize != 0 ){
    		$sql .= " LIMIT $nextNum,$pagesize";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds);
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return $result;
    	}
		
		$sql = "select count(*) FROM `".self::$table . "` AS a,`".self::$category_table."` AS b WHERE a.category_id = b.category_id AND a.delete_flag = 0";
		$binds2 = array();
   		if(is_array($condition)&& count($condition)>0){
    		if(isset($condition['category_id'])) {
    			$sql .= " AND a.`category_id`=:category_id";
    			$binds2[':category_id'] = $condition['category_id'];
    		}
    		$keywords = trim($condition['keyword']);
    		if(!empty($keywords)) {
    			if($condition['search_type'] == 'title') {
    				$sql .= " AND a.`title` like '%".$keywords."%'";
    			} else {
    				$sql .= " AND a.`content` like '%".$keywords."%'";
    			}
    		}
    	}
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	/**
	* @desc 获取用户分类表信息
	* @param void
	* @return array()
	* @author kevin wang
	* @date 2014-09-19
	*/
	public static function get_user_category(){
		$sql = "select `user_category_id`,`parent_id`,`category_name`,`depth` from `weixin_user_category` where `parent_id` = :parent_id and `delete_flag` = :delete_flag";
		$binds = array(':parent_id' => 0, ':delete_flag' => 0);
		$st = self::DB()->dbh->prepare($sql);
    	$st->execute($binds);
    	$result = $st->fetchAll();
		if(empty($result)){
			return $result;
		}
		foreach($result as $k=>$v){
			$binds = array(':parent_id' => $v['user_category_id'], ':delete_flag' => 0);
			$st->execute($binds);
			$result[$k]['child'] = $st->fetchAll();
		}
		return $result;
	}
	//获取新闻的用户分类
	public static function get_user_type_of_news($id){
		$result = array();
		if(empty($id)){
			return $result;
		}
		$sql = "select `category_id` from `weixin_news_category` where `news_id` = :news_id and `delete_flag` = 0";
		try{
			$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(":news_id" => $id));
    		$result = $st->fetchAll();
		}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $result;
	}
}
